package com.lengyue.imakiba.base;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.jfinal.plugin.activerecord.Record;

public class ImportExcel {
	//excel字段
	String[] fields;
	//excel字段名
	String[] names;
	//数据起始行
	int dataStartRow = 0;
	//数据列数
	int fieldLength = 0;
	/**
	 * 解析excel文件，返回record模型
	 * @param file excel文件
	 * @return record数组
	 * @throws Exception
	 */
	public Record[] importData(File file) throws Exception
	{
		//FileInputStream fis = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(file);
		/*Workbook workbook = null;
		try {
			workbook = new XSSFWorkbook(fis);
		} catch (Exception e) {
			workbook = new HSSFWorkbook(fis);
		}*/
		//只取第一个表格、其他表格忽略
		Sheet sheet = workbook.getSheetAt(0);
		int rowNum = sheet.getPhysicalNumberOfRows();
		//初始化excel字段，名称，数据起始行
		initFieldAndNameAndDataStartRow(sheet);
		//
		ArrayList<Record> recordList = new ArrayList<Record>();
		for(int i=dataStartRow;i<rowNum;i++)
		{
			Record record = new Record();
			for(int j=0;j<fieldLength;j++)
			{
				record.set(fields[j], getCellData(sheet.getRow(i).getCell(j), null));
			}
			recordList.add(record);
		}
		return recordList.toArray(new Record[recordList.size()]);
	}
	/**
	 * 表单第一行为字段，第二行为字段名称，第三行为数据起始行
	 * 取需要导入的
	 * @param sheet
	 */
	private void initFieldAndNameAndDataStartRow(Sheet sheet)
	{
		Row fieldRow = sheet.getRow(0);
		Row nameRow = sheet.getRow(1);
		fieldLength = fieldRow.getPhysicalNumberOfCells();
		ArrayList<String> fieldList = new ArrayList<String>();
		ArrayList<String> nameList = new ArrayList<String>();
		for(int i=0;i<fieldLength;i++)
		{
			fieldList.add(fieldRow.getCell(i).getRichStringCellValue().getString());
			nameList.add(nameRow.getCell(i).getRichStringCellValue().getString());
		}
		fields = fieldList.toArray(new String[fieldLength]);
		names = nameList.toArray(new String[fieldLength]);
		dataStartRow = 2;
	}
	/**
	 * 取cell的值，根据cell类型返回不同类型的值
	 * @param cell 单元格
	 * @param formula 公式校验器
	 * @return cell的值
	 */
	private Object getCellData(Cell cell, FormulaEvaluator formula) {
	    if(cell == null) {
	        return null;
	    }
	    switch (cell.getCellType()) {
	    case Cell.CELL_TYPE_STRING:
	        return cell.getRichStringCellValue().getString();
	    case Cell.CELL_TYPE_NUMERIC:
	        if (DateUtil.isCellDateFormatted(cell)) {
	            return cell.getDateCellValue();
	        } else {
	            return (int)cell.getNumericCellValue();//数字类型强制转换为int型。
	        }
	    case Cell.CELL_TYPE_BOOLEAN:
	        return cell.getBooleanCellValue();
	    case Cell.CELL_TYPE_FORMULA:
	        return formula.evaluate(cell).getNumberValue();
	    default:
	        return null;
	    }
	}
}
